package com.pentofab.payrollmanagement.database.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import com.pentofab.payrollmanagement.dao.model.UserAccounts;
import com.pentofab.payrollmanagement.database.UserAccountsRepository;
import com.pentofab.payrollmanagement.exception.BusinessException;
import com.pentofab.payrollmanagement.exception.ExceptionMsg;
import com.pentofab.payrollmanagement.utilities.PayRollUtility;

public class UserAccountsRepositoryImpl implements UserAccountsRepository {

	
	Connection conn = null;

	public UserAccountsRepositoryImpl() {

		conn = CreateDBConnection.cteateConnectionObj();
	
	}
	@Override
	public void createUserAccount(UserAccounts accounts) throws Exception {
		
		PreparedStatement preparedStatement = conn
				.prepareStatement(UserAccountsTableSQLConstants.createAccount);
		preparedStatement.setString(1,accounts.getUsername());
		preparedStatement.setString(2, accounts.getPassword());
		preparedStatement.setString(3, accounts.getCreatedby());
		preparedStatement.setString(4, accounts.getCreationdate());
		preparedStatement.setString(5, accounts.getModifiedby());
		preparedStatement.setString(6, accounts.getModificationdate());
		preparedStatement.setString(7, accounts.getValidity());
		preparedStatement.setString(8, accounts.getPermissions());
		preparedStatement.setBoolean(9, true);
		preparedStatement.setString(10, accounts.getQuestion());
		preparedStatement.setString(11, accounts.getAnswer());
		try{
		preparedStatement.executeUpdate();
		}catch(MySQLIntegrityConstraintViolationException exception){
			throw new BusinessException(ExceptionMsg.USER_EXISTS);
		}catch(Exception e){
			e.printStackTrace();
			throw new BusinessException(USER_ACCOUNT_CREATE_ERROR);
		}
	}

	@Override
	public void deleteUserAccount(String username) throws Exception {
	
	
		PreparedStatement preparedStatement = conn.prepareStatement(UserAccountsTableSQLConstants.deleteUser);
		preparedStatement.setBoolean(1, true);
		preparedStatement.setString(2, username);
		try{
			preparedStatement.executeUpdate();
			}catch(Exception e){
				throw new BusinessException(USER_ACCOUNT_DELET_ERROR);
			}
	}

	@Override
	public void modifyUserAccount(UserAccounts accounts, String oldUserName) throws Exception {
		
		try {

			PreparedStatement preparedStatement = conn
					.prepareStatement(UserAccountsTableSQLConstants.modifyUserAccounts);
			preparedStatement.setString(1, accounts.getUsername());
			preparedStatement.setString(2, accounts.getPassword());
			preparedStatement.setString(3, accounts.getCreatedby());
			preparedStatement.setString(4, accounts.getCreationdate());
			preparedStatement.setString(5, accounts.getModifiedby());
			preparedStatement.setString(6, accounts.getModificationdate());
			preparedStatement.setString(7, accounts.getValidity());
			preparedStatement.setString(8, accounts.getPermissions());
			preparedStatement.setBoolean(9, true);
			preparedStatement.setString(10, accounts.getQuestion());
			preparedStatement.setString(11, accounts.getAnswer());
			preparedStatement.setString(12, oldUserName);
			
			PayRollUtility.debug("modifyUserAccount preparedStatement : "+preparedStatement);
			
			preparedStatement.executeUpdate();
		}catch(MySQLIntegrityConstraintViolationException exception){
				throw new BusinessException(ExceptionMsg.USER_EXISTS);
			}catch(Exception e){
				throw new BusinessException(USER_ACCOUNT_MODIFY_ERROR);
			}
		
	}

	@Override
	public void getUserAccountsByParent(String parentUserName, ArrayList<UserAccounts> userAccList)throws Exception {

		PreparedStatement preparedStatement = conn
				.prepareStatement(UserAccountsTableSQLConstants.getAccountsByParent);
		preparedStatement.setString(1, parentUserName);
		preparedStatement.setBoolean(2, false);
		System.out.println(preparedStatement);
		ResultSet rs = preparedStatement.executeQuery();
		if (!rs.isBeforeFirst()) {
			return;
		}
		
		while(rs.next()){
			
			UserAccounts acc = new UserAccounts();
			acc.setUsername(rs.getString(UserAccountsTableSQLConstants.username));
			acc.setPassword(rs.getString(UserAccountsTableSQLConstants.password));
			acc.setCreatedby(rs.getString(UserAccountsTableSQLConstants.createdby));
			acc.setCreationdate(rs.getString(UserAccountsTableSQLConstants.creationdate));
			acc.setModifiedby(rs.getString(UserAccountsTableSQLConstants.modifiedby));
			acc.setModificationdate(rs.getString(UserAccountsTableSQLConstants.modificationdate));
			acc.setPermissions(rs.getString(UserAccountsTableSQLConstants.permissions));
			acc.setValidity(rs.getString(UserAccountsTableSQLConstants.validity));
			acc.setActive(rs.getBoolean(UserAccountsTableSQLConstants.active));
			acc.setQuestion(rs.getString(UserAccountsTableSQLConstants.securityQuestion));
			acc.setAnswer(rs.getString(UserAccountsTableSQLConstants.answer));
			
			
			userAccList.add(acc);
			getUserAccountsByParent(acc.getUsername(),userAccList);
		}
	}
		
	@Override
	public UserAccounts getUserAccountsByUserName(String userName) throws Exception {


		PreparedStatement preparedStatement = conn.prepareStatement(UserAccountsTableSQLConstants.getAccountsByUserName);
		preparedStatement.setString(1, userName);
		preparedStatement.setBoolean(2, false);
		
		System.out.println(preparedStatement);
		
		ResultSet rs = preparedStatement.executeQuery();
		
		if (!rs.isBeforeFirst()) {
			return null;
		}
		
		if(rs.next()){
			
			UserAccounts acc = new UserAccounts();
			
			acc.setUsername(rs.getString(UserAccountsTableSQLConstants.username));
			acc.setPassword(rs.getString(UserAccountsTableSQLConstants.password));
			acc.setCreatedby(rs.getString(UserAccountsTableSQLConstants.createdby));
			acc.setCreationdate(rs.getString(UserAccountsTableSQLConstants.creationdate));
			acc.setModifiedby(rs.getString(UserAccountsTableSQLConstants.modifiedby));
			acc.setModificationdate(rs.getString(UserAccountsTableSQLConstants.modificationdate));
			acc.setPermissions(rs.getString(UserAccountsTableSQLConstants.permissions));
			acc.setValidity(rs.getString(UserAccountsTableSQLConstants.validity));
			acc.setActive(rs.getBoolean(UserAccountsTableSQLConstants.active));
			acc.setQuestion(rs.getString(UserAccountsTableSQLConstants.securityQuestion));
			acc.setAnswer(rs.getString(UserAccountsTableSQLConstants.answer));
			
			return acc;
		}
		return null;
		
	}
	public static void main(String[] args) {

	System.out.println(UserAccountsTableSQLConstants.modifyUserAccounts);
	}
}
